Using Stored Procedures with Oracle

SequeLink supports stored procedures against Oracle, including stored procedures in packages.

NOTE: Stored procedures in packages must be qualified with the package name, for example, EmployeePackage.EmployeeProc.

Also, SQLProcedures and SQLProcedureColumns can return information on procedures within PL/SQL packages, allowing ODBC applications to execute these procedures. This section contains an example that shows you how to fetch rows using Oracle PL/SQL procedures.

Example - Part 1

Create or replace package EmployeeInfo as 
   Type EmployeeRec is record 
   ( 
   Employee_Id     integer, 
   Employee_Name   varchar2(25), 
   Employee_Job    varchar2(25), 
   Department_Name varchar2(30), 
   Employee_Salary integer 
   ); 
   Type EmployeeCursor is ref cursor return  
   EmployeeRec; 
   End EmployeeInfo; 
    
   Create or replace procedure EmployeeInfoProc 
   (empname IN varchar2, empcursor IN OUT  
   EmployeeInfo.EmployeeCursor) 
   As 
   Begin 
   Open empcursor For 
   select empno, ename, job, dname, sal  
   from emp, dept 
   where emp.deptno=dept.deptno and  
   ename like empname; 
   End; 

NOTE: In this Oracle PL/SQL package, a record type and a cursor (result set) type is defined. The procedure contains an input parameter that can have a value, such as Smi%, to request information about employees whose last name starts with the letters 'Smi' (for example, Smith or Smithwick). The procedure also has one input/output parameter of the cursor type defined in the package.

Example - Part 2

This example shows an ODBC function call sequence executing the stored procedure.

SQLPrepare(...,'{call EmployeeInfoProc(?)}',...)  
                         <- ODBC SQL syntax to executed stored procedures 
SQLBindParameter(...,'Smi%',...)  
                         <- Define the input variable for the input marker ? 
                            in the SQL stmt and assign the value 'Smi% to it 
SQLExecute()             <- Execute the stored procedure 
SQLBindCol()             <- Assign storage for result column 1 in the  
                            result set (Employee_Id) 
SQLBindCol()             <- Assign storage for result column 2 in the  
                            result set (Employee_Name) 
SQLBindCol()             <- Assign storage for result column 3 in the  
                            result set (Employee_Job) 
SQLBindCol()             <- Assign storage for result column 4 in the  
                            result set (Department_Name) 
SQLBindCol()             <- Assign storage for result column 5 in the  
                            result set (Employee_Salary) 
SQLFetch()               <- Fetch the first record from the result set  
                            generated by the stored procedure. 

IMPORTANT: From the following procedure definition, you might think that, by having two parameters, the procedure must call SQLBindParameter twice:

Create or replace procedure EmployeeInfoProc 
(empname IN varchar2, empcursor IN OUT 
EmployeeInfo.EmployeeCursor) 

Actually, it does not. The only way to create a result set from an Oracle stored procedure is to declare this result set, empcursor, as an input/output parameter. This can be seen in the result of SQLProcedureColumns(...,'EmployeeInfoProc',...) which an application can use to query the server about a stored procedure.

The following is an excerpt of a session using the tool ODBCTest:

SQLAllocStmt: 
   In: hdbc=0x004609F0, phstmt=VALID 
   Return:    SQL_SUCCESS=0 
   SQLPrepare: 
   In: hstmt=#3 0x00305850, szSqlStr={call EmployeeInfoProc(?)}, cbSqlStr=26 
   Return:    SQL_SUCCESS=0 
   SQLBindParameter: 
   In: hstmt=#3 0x00305850, ipar=1, fParamType=SQL_PARAM_INPUT=1,  
   fCType=SQL_C_CHAR=1, 
   fSqlType=SQL_CHAR=1, cbColDef=10, ibScale=0, rgbValue=VALID,  
   cbValueMax=300, pcbValue=VALID, SQL_LEN_DATA_AT_EXEC=FALSE 
   Return:    SQL_SUCCESS=0 
   SQLExecute: 
   In: hstmt=#3 0x00305850 
   Return:    SQL_SUCCESS=0 
   Get Data All: 
   "EMPNO", "ENAME", "JOB", "DNAME", "SAL" 
   7934, "MILLER", "CLERK", "ACCOUNTING", 1300.00  
   7654, "MARTIN", "SALESMAN", "SALES", 1250.00 
   2 rows fetched from 5 columns. 
   SQLProcedureColumns: 
   In: hstmt=#4 0x00305BD8, ...Qualifier=NULL, ...Qualifier=0,  
   Owner=SCOTT, ...Owner=5, ...Name=EMPLOYEEINFOPROC, 
   ...Name=16, ...Name=NULL, ...Name=0 
   Return:    SQL_SUCCESS=0 
   Get Data All: 
   "PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "COLUMN_NAME", 
   "COLUMN_TYPE", "DATA_TYPE", ..."TYPE_NAME", "COLUMN_SIZE", 
   "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", 
   "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", 
   "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE" 
   "", "SCOTT", "EMPLOYEEINFOPROC", "EMPNAME", 1, 12, "VARCHAR2", 2000,  
   2000, <Null>, <Null>, 1, <Null>, <Null>, ...12, <Null>, 2000, 1, "YES"